# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import mysql.connector
from shopSpider.items import ShopListItem
from shopSpider.items import GoodsListItem
from shopSpider.items import GoodsDetailItem


class ShopspiderPipeline:
    def __init__(self, host, port, dbname, user, password):
        self.host = host
        self.port = port
        self.dbname = dbname
        self.user = user
        self.password = password

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get('MYSQL_HOST'),
            port=crawler.settings.get('MYSQL_PORT'),
            dbname=crawler.settings.get('MYSQL_DBNAME'),
            user=crawler.settings.get('MYSQL_USER'),
            password=crawler.settings.get('MYSQL_PASSWORD'),
        )

    def open_spider(self, spider):
        self.conn = mysql.connector.connect(
            host=self.host,
            port=self.port,
            database=self.dbname,
            user=self.user,
            password=self.password,
        )
        self.cursor = self.conn.cursor()

    def close_spider(self, spider):
        self.conn.commit()
        self.conn.close()

    def process_item(self, item, spider):
        if isinstance(item, ShopListItem):
            sql = 'INSERT INTO taobao_shop_list (title, user_num_id, zhuy,nick,shop_Id,type,pic_url) VALUES (%s, %s, %s,%s, %s,%s, %s)'
            self.cursor.execute(sql, (
                item['title'],
                item['user_num_id'],
                item['zhuy'],
                item['nick'],
                item['shop_Id'],
                item['type'],
                item['pic_url'],
            ))
        elif isinstance(item, GoodsListItem):
            sql = 'INSERT INTO taobao_goods_list (title, num_iid, promotion_price,price,sales,seller_id,shop_id,shop_name,pic_url,detail_url) VALUES (%s, %s, %s,%s, %s,%s, %s, %s,%s, %s)'
            self.cursor.execute(sql, (
                item['title'],
                item['num_iid'],
                item['promotion_price'],
                item['price'],
                item['sales'],
                item['seller_id'],
                item['shop_id'],
                item['shop_name'],
                item['pic_url'],
                item['detail_url'],
            ))
        elif isinstance(item, GoodsDetailItem):
            sql = 'INSERT INTO taobao_goods_detail (title, num_iid, desc_short,price,orginal_price,nick,num,pic_url,brand,brandId,detail_url,desc,item_imgs,prop_imgs,props,props_name,skus,props_list,seller_info,seller_id,shop_id) VALUES (%s, %s, %s,%s, %s,%s, %s, %s,%s, %s, %s,%s, %s,%s, %s, %s,%s,%s, %s,%s,%s,%s)'
            self.cursor.execute(sql, (
                item['title'],
                item['num_iid'],
                item['desc_short'],
                item['price'],
                item['orginal_price'],
                item['nick'] ,
                item['num'] ,
                item['pic_url'],
                item['brand'],
                item['brandId'],
                item['detail_url'],
                item['desc'],
                item['item_imgs'],
                item['prop_imgs'],
                item['props'],
                item['props_name'],
                item['skus'],
                item['props_list'],
                item['seller_info'],
                item['seller_id'],
                item['shop_id']
            ))
        return item